const mysql = require('mysql2/promise'),
      {con,ResData} = require('./config.js');

// 用户数据的获取、登录
async function getUsers(){
  const res = new ResData();
  const sql = 'select * from users order by user_id;',
        sqlCount = 'select count(user_id) as count from users;';
  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 新用户的注册（数据的插入）
async function insertUsers(user_name,user_password){
  const sql = "insert into users(user_name,user_password) values(?,?);";
  await con.query(sql,[user_name,user_password],function(err,results){});
  return;
}

// 指定用户数据的删除
async function delUsers(user_id){
  const sql = `delete from users where user_id = ${user_id};`;
  await con.execute(sql);
  return;
}

// 指定用户数据的更新
async function updateUsers(user_id,user_name,user_password,user_introduction,user_head_portrait,user_design_cost,user_style,user_cultural_experience,user_curriculum_vitae){
  const sql = "update users set ??=?,??=?,??=?,??=?,??=?,??=?,??=?,??=? where ??=?;";
  await con.query(sql,['user_name',user_name,'user_password',user_password,'user_introduction',user_introduction,'user_head_portrait',user_head_portrait,"user_design_cost",user_design_cost,"user_style",user_style,"user_cultural_experience",user_cultural_experience,"user_curriculum_vitae",user_curriculum_vitae,'user_id',user_id],function(err,results){});
  return;
}


// 管理员的后台登录
async function getAdmins(){
  const res = new ResData();
  const sql = 'select * from admins order by admins_name;',
        sqlCount = 'select count(admins_name) as count from admins;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 用户设计数据的获取
async function getUserDesignDatas(){
  const res = new ResData();
  const sql = 'select * from user_design_datas order by design_id;',
        sqlCount = 'select count(design_id) as count from user_design_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 用户设计数据的上传
async function insertUserDesignDatas(user_id,design_image,design_name,design_content){
  const sql = "insert into user_design_datas(user_id,design_image,design_name,design_content) values(?,?,?,?);";
  await con.query(sql,[user_id,design_image,design_name,design_content],function(err,results){});
  return;
}

// 用户设计数据的删除
async function delUserDesignDatas(design_id){
  const sql = `delete from user_design_datas where design_id = ${design_id};`;
  await con.execute(sql);
  return;
}

// 模型素材的获取
async function getModelDatas(){
  const res = new ResData();
  const sql = 'select * from model_datas order by model_id;',
        sqlCount = 'select count(model_id) as count from model_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 模型素材的删除
async function delModelDatas(model_id){
  const res = new ResData();
  const sql = `delete from model_datas where model_id = "${model_id}";`;
  try{
    let [rows] = await con.execute(sql);
    res.msg = rows.affectedRows === 1 ? 'delete succeed' : 'delete failed'
    return res;              
  } catch(e){
    res.code = 403003;
    res.msg = 'delete failed';
    return res;              
  }
}

// 绘制素材的获取
async function getDrawDatas(){
  const res = new ResData();
  const sql = 'select * from draw_datas order by draw_id;',
        sqlCount = 'select count(draw_id) as count from draw_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 绘制素材的删除
async function delDrawDatas(draw_id){
  const res = new ResData();
  const sql = `delete from draw_datas where draw_id = "${draw_id}";`;
  try{
    let [rows] = await con.execute(sql);
    res.msg = rows.affectedRows === 1 ? 'delete succeed' : 'delete failed'
    return res;              
  } catch(e){
    res.code = 403003;
    res.msg = 'delete failed';
    return res;              
  }
}

// 灵感关注数据的获取
async function getInspirationAttentionDatas(user_inspiration_id){
  const res = new ResData();
  const sql = "select * from inspiration_attention_datas where user_inspiration_id=?;",
        sqlCount = 'select count(user_id) as count from inspiration_attention_datas;';

  let [rows] = await con.query(sql,[user_inspiration_id],function(err,results){});
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 灵感关注数据的插入
async function insertInspirationAttentionDatas(user_inspiration_id,user_id,design_id){
  const sql = "insert into inspiration_attention_datas(user_inspiration_id,user_id,design_id) values(?,?,?);";
  await con.query(sql,[user_inspiration_id,user_id,design_id],function(err,results){});
  return;
}

// 灵感关注数据的删除
async function delInspirationAttentionDatas(user_inspiration_id,design_id,user_id){
  const sql = `delete from inspiration_attention_datas where user_inspiration_id=${user_inspiration_id} and design_id=${design_id} and user_id=${user_id};`;
  await con.execute(sql);
  return;
}

// 创作资源的获取
async function getCreationResourcesDatas(){
  const res = new ResData();
  const sql = 'select * from creation_resources_datas order by create_id;',
        sqlCount = 'select count(create_id) as count from creation_resources_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 创作资源的删除
async function delCreationResourcesDatas(create_id){
  const res = new ResData();
  const sql = `delete from creation_resources_datas where create_id = "${create_id}";`;
  try{
    let [rows] = await con.execute(sql);
    res.msg = rows.affectedRows === 1 ? 'delete succeed' : 'delete failed'
    return res;              
  } catch(e){
    res.code = 403003;
    res.msg = 'delete failed';
    return res;                                
  }
}

// 轮播图的获取
async function getShowDatas(){
  const res = new ResData();
  const sql = 'select * from show_datas order by show_id;',
        sqlCount = 'select count(show_id) as count from show_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 产品征集博物馆信息的获取
async function getProductsCollectionMuseumDatas(){
  const res = new ResData();
  const sql = 'select * from products_collection_museum_datas order by museum_id;',
        sqlCount = 'select count(museum_id) as count from products_collection_museum_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 产品征集比赛活动的获取
async function getProductsCollectionMatchDatas(){
  const res = new ResData();
  const sql = 'select * from products_collection_match_datas order by match_id;',
        sqlCount = 'select count(match_id) as count from products_collection_match_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 帮助中心数据的获取
async function getHelpCenterDatas(con_title){
  const res = new ResData();
  const sql = `select * from help_center_datas where help_con_title = ${con_title} order by help_order;`,
        sqlCount = 'select count(help_order) as count from help_center_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 用户上传素材的获取
async function getUserUploadDatas(){
  const res = new ResData();
  const sql = 'select * from user_upload_datas order by upload_id',
        sqlCount = 'select count(upload_id) as count from user_upload_datas;';

  let [rows] = await con.execute(sql);
  res.data = rows;
  [rows] = await con.execute(sqlCount);
  res.count = rows[0].count;
  return res;
}

// 用户上传素材
async function addUserUploadDatas(user_id,upload_image){
  let sql = `insert into user_upload_datas(user_id,upload_image) values("${user_id}","${upload_image}");`,
      res = new ResData();
  try{
    let [rows] = await con.execute(sql);
    res.msg = rows.affectedRows === 1 ? '添加成功': '添加失败';
    return res;
  } catch(e){
    res.code = 403001;
    res.msg  = '添加失败';
    return res;          
  }
}

// 用户上传素材的删除
async function delUserUploadDatas(upload_id){
  const res = new ResData();
  const sql = `delete from user_upload_datas where upload_id = "${upload_id}";`;
  try{
    let [rows] = await con.execute(sql);
    res.msg = rows.affectedRows === 1 ? 'delete succeed' : 'delete failed'
    return res;              
  } catch(e){
    res.code = 403003;
    res.msg = 'delete failed';
    return res;                                
  }
}


module.exports = {
  getUsers,
  insertUsers,
  delUsers,
  updateUsers,

  getAdmins,

  getUserDesignDatas,
  insertUserDesignDatas,
  delUserDesignDatas,

  getModelDatas,
  delModelDatas,

  getDrawDatas,
  delDrawDatas,

  getInspirationAttentionDatas,
  insertInspirationAttentionDatas,
  delInspirationAttentionDatas,

  getCreationResourcesDatas,
  delCreationResourcesDatas,

  getShowDatas,

  getProductsCollectionMuseumDatas,
  getProductsCollectionMatchDatas,

  getHelpCenterDatas,

  getUserUploadDatas,
  addUserUploadDatas,
  delUserUploadDatas
}
